from typing import List, Dict

import sqlalchemy.exc
from fastapi import APIRouter, Depends
from fastapi.exceptions import HTTPException
from sqlalchemy import delete, select, or_, update

from models.students import Student
from models import AsyncSessionFactory
from schemas.students import StudentReqSchema,StudentRespSchema,StudentUpdateReqSchema
from models import AsyncSession
from fastapi.requests import Request

router=APIRouter(prefix="/student",tags=["student"])

# 异步上下文管理器创建session
@router.post("/add",response_model=StudentRespSchema)
async def add_student(student_data: StudentReqSchema):
    async with AsyncSessionFactory() as session:
        try:
            async with session.begin():
                student = Student(sno=student_data.sno, name=student_data.name, grade=student_data.grade,email=student_data.email)
                session.add(student)
        except Exception as e:
            # 如果抛出异常，异步上下文管理器就已经执行了回滚操作
            raise HTTPException(status_code=400,detail={'msg':'该学生的姓名或邮箱已存在,添加失败','tag':''})
    return student

# 依赖项创建session
async  def get_session():
    session = AsyncSessionFactory()
    try:
        yield session
    finally:
        await session.close()

@router.post('/add/depends',response_model=StudentRespSchema)
async def add_student_depends(
        student_data: StudentReqSchema,
        session:AsyncSession=Depends(get_session)
):
    try:
        async with session.begin():
            student = Student(sno=student_data.sno, name=student_data.name, grade=student_data.grade,
                              email=student_data.email)
            session.add(student)
        return student
    except Exception as e:
        # 如果抛出异常，异步上下文管理器就已经执行了回滚操作
        raise HTTPException(status_code=400,detail={'msg':'该学生的姓名或邮箱已存在,添加失败','tag':'depends'})

@router.post('/add/middleware',response_model=StudentRespSchema)
async def add_student_middleware(request: Request,student_data: StudentReqSchema):
    session = request.state.session
    try:
        async with session.begin():
            student = Student(sno=student_data.sno, name=student_data.name, grade=student_data.grade,email=student_data.email)
            session.add(student)
        return student
    except Exception as e:
        raise HTTPException(status_code=400,detail={'msg':'该学生的姓名或邮箱已存在,添加失败','tag':'middleware'})

@router.delete('/delete/{sno}')
async def delete_student(sno:int,request:Request):
    session = request.state.session
    try:
        async with session.begin():
            sql=delete(Student).where(Student.sno==sno)
            await session.execute(sql)
        return {'message':'删除成功'}
    except Exception as e:
        raise HTTPException(status_code=400,detail={'msg':'删除失败'})

# 查找一条数据
@router.get('/select/{sno}')
async def select_student(sno:int,request:Request):
    session = request.state.session
    async with session.begin():
        stmt=select(Student).where(Student.sno==sno)
        query=await session.execute(stmt)
        result=query.one()._asdict()
        return result

# 查找多条数据
@router.get('/select/list/{q}')
async def select_list_students(request:Request,q:str):
    session = request.state.session
    async with session.begin():
        stmt=select(Student.id,Student.sno,Student.name,Student.email).where(
            or_(Student.name.contains(q),Student.email.contains(q))
        ).offset(0).limit(5).order_by(Student.id.desc())
        query=await session.execute(stmt)
        rows = [row._asdict() for row in query]
        return rows

# 更新学生信息
@router.put('/update/{sno}')
async def update_student(sno:int,request:Request,student_data:StudentUpdateReqSchema):
    session = request.state.session
    async with session.begin():
        fields: Dict={}
        if student_data.name:
            fields['name'] = student_data.name
        if student_data.email:
            fields['email'] = student_data.email
        stmt=update(Student).where(Student.sno==sno).values(**fields)
        await session.execute(stmt)
    return {'message':'数据更新成功'}